﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Mulberry.Model;
using OMG.Fill.Mulberry.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Mulberry.Service
{
    /// <summary>
    /// 表9套种作物收益调查情况T_MulCropIncome通用操作类
    /// </summary>
    public class MulCropIncomeServices:MulCropIncomes
    {
        DictServices dict = new DictServices();
        public Pager<List<MulCropIncomeEntity>> GetPageList(MulCropIncomeQuery query)
        {
            Pager<List<MulCropIncomeEntity>> obj = new Pager<List<MulCropIncomeEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_MulCropIncome where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<MulCropIncomeEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表9套种作物收益调查情况T_MulCropIncome分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 获取指定对象
        /// </summary>
        public ResResult<List<MulCropIncomeEntity>> GetList(string ReportId)
        {
            ResResult<List<MulCropIncomeEntity>> obj = new ResResult<List<MulCropIncomeEntity>>();
            MulCropIncomeEntity model = new MulCropIncomeEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = new List<MulCropIncomeEntity>();
                    obj.model.Add(model);
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_MulCropIncome where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                       List< MulCropIncomeEntity > list= dbCon.Query<MulCropIncomeEntity>(strSql, new { ReportId = ReportId }).ToList();
                        //if(list.Count>0)
                        //{
                        //    foreach(var item in list)
                        //    {
                        //        if(!string.IsNullOrEmpty(item.InterCropName))
                        //        {
                        //            item.InterCropName = dict.GetPZName(item.InterCropName, "InterCrop");
                        //        }
                        //    }
                        //}
                        obj.model = list;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取表1桑园面积统计表T_MulCropIncome对象信息失败", ex);

                }
            }
            return obj;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_MulCropIncome where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("判断是否生成过表1桑园面积统计表T_MulCropIncome失败", ex);
                return false;
            }
        }
        public ResResult<int> AddInfo(List<MulCropIncomeEntity> list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_MulCropIncome";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "HouseName,";
            strSql += "InterCropName,";
            strSql += "SowTime,";
            strSql += "HarvestTime,";
            strSql += "InterArea,";
            strSql += "UseSeed,";
            strSql += "SeedPrice,";
            strSql += "AddFertili,";
            strSql += "FertilizerPrice,";
            strSql += "fertilizerPerMu,";
            strSql += "FullGrowEmploy,";
            strSql += "LocalWages,";
            strSql += "HarvestYield,";
            strSql += "SalePrice,";
            strSql += "AveIncome)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@HouseName,";
            strSql += "@InterCropName,";
            strSql += "@SowTime,";
            strSql += "@HarvestTime,";
            strSql += "@InterArea,";
            strSql += "@UseSeed,";
            strSql += "@SeedPrice,";
            strSql += "@AddFertili,";
            strSql += "@FertilizerPrice,";
            strSql += "@fertilizerPerMu,";
            strSql += "@FullGrowEmploy,";
            strSql += "@LocalWages,";
            strSql += "@HarvestYield,";
            strSql += "@SalePrice,";
            strSql += "@AveIncome)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        list[i].Id = CustomFun.GetGuid();
                    }

                    string strDel = "delete from dbo.T_MulCropIncome where ReportId=@ReportId";
                    dbCon.Execute(strDel, new { ReportId = list[0].ReportId }, trans);
                    int result = dbCon.Execute(strSql, list, trans);
                    string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                    dbCon.Execute(strRep, new { ReportId = list[0].ReportId, UpdateTime = DateTime.Now }, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;

                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表4全年饲养蚕品种构成调查表T_MulCropIncome对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
        }

        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<MulCropInfoModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<MulCropInfoModel> list = new List<MulCropInfoModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    MulCropInfoModel model = new MulCropInfoModel();
                    if (row.GetCell(0) != null && row.GetCell(0).ToString() != "")
                    {
                        model.HouseName = row.GetCell(0).ToString();
                    }
                    else
                    {
                        model.HouseName = null;
                    }
                    if (row.GetCell(1) != null && row.GetCell(1).ToString() != "")
                    {
                        //model.InterCropName = row.GetCell(2).ToString();
                        model.InterCropName = dict.GetPZKey(row.GetCell(1).ToString(), "InterCrop");
                    }
                    else
                    {
                        model.InterCropName = null;
                    }
                    if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                    {
                        model.SowTime = row.GetCell(2).ToString();
                    }
                    else
                    {
                        model.SowTime = null;
                    }
                    if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                    {
                        model.HarvestTime = row.GetCell(3).ToString();
                    }
                    else
                    {
                        model.HarvestTime = null;
                    }
                    if (row.GetCell(4) != null && row.GetCell(4).ToString() != "")
                    {
                        model.InterArea = decimal.Parse(row.GetCell(4).ToString());
                    }
                    else
                    {
                        model.InterArea = null;
                    }
                    if (row.GetCell(5) != null && row.GetCell(5).ToString() != "")
                    {
                        model.UseSeed = decimal.Parse(row.GetCell(5).ToString());
                    }
                    else
                    {
                        model.UseSeed = null;
                    }
                    if (row.GetCell(6) != null && row.GetCell(6).ToString() != "")
                    {
                        model.SeedPrice = decimal.Parse(row.GetCell(6).ToString());
                    }
                    else
                    {
                        model.SeedPrice = null;
                    }
                    if (row.GetCell(7) != null && row.GetCell(7).ToString() != "")
                    {
                        model.AddFertili = decimal.Parse(row.GetCell(7).ToString());
                    }
                    else
                    {
                        model.AddFertili = null;
                    }
                    if (row.GetCell(8) != null && row.GetCell(8).ToString() != "")
                    {
                        model.FertilizerPrice = decimal.Parse(row.GetCell(8).ToString());
                    }
                    else
                    {
                        model.FertilizerPrice = null;
                    }
                    if (row.GetCell(9) != null && row.GetCell(9).ToString() != "")
                    {
                        model.FullGrowEmploy = decimal.Parse(row.GetCell(9).ToString());
                    }
                    else
                    {
                        model.FullGrowEmploy = null;
                    }
                    if (row.GetCell(10) != null && row.GetCell(10).ToString() != "")
                    {
                        model.LocalWages = decimal.Parse(row.GetCell(10).ToString());
                    }
                    else
                    {
                        model.LocalWages = null;
                    }
                    if (row.GetCell(11) != null && row.GetCell(11).ToString() != "")
                    {
                        model.HarvestYield = decimal.Parse(row.GetCell(11).ToString());
                    }
                    else
                    {
                        model.HarvestYield = null;
                    }
                    if (row.GetCell(12) != null && row.GetCell(12).ToString() != "")
                    {
                        model.SalePrice = decimal.Parse(row.GetCell(12).ToString());
                    }
                    else
                    {
                        model.SalePrice = null;
                    }
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(MulCropInfoModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_MulCropIncome";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "HouseName,";
            strSql += "InterCropName,";
            strSql += "SowTime,";
            strSql += "HarvestTime,";
            strSql += "InterArea,";
            strSql += "UseSeed,";
            strSql += "SeedPrice,";
            strSql += "AddFertili,";
            strSql += "FertilizerPrice,";
            strSql += "fertilizerPerMu,";
            strSql += "FullGrowEmploy,";
            strSql += "LocalWages,";
            strSql += "HarvestYield,";
            strSql += "SalePrice,";
            strSql += "AveIncome)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@HouseName,";
            strSql += "@InterCropName,";
            strSql += "@SowTime,";
            strSql += "@HarvestTime,";
            strSql += "@InterArea,";
            strSql += "@UseSeed,";
            strSql += "@SeedPrice,";
            strSql += "@AddFertili,";
            strSql += "@FertilizerPrice,";
            strSql += "@fertilizerPerMu,";
            strSql += "@FullGrowEmploy,";
            strSql += "@LocalWages,";
            strSql += "@HarvestYield,";
            strSql += "@SalePrice,";
            strSql += "@AveIncome)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增表9套种作物收益调查情况T_MulCropIncome对象信息失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        public virtual ResResult<int> DeleteData(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_MulCropIncome where ReportId = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除表9套种作物收益调查情况T_MulCropIncome对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        ///  桑园套种收益情况--------(按年度统计)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulCropStatsModel>> GetmulQuartProducStatsList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetPZKey(model.FillInYear, "ReportYear");
            }
            ResResult<List<MulCropStatsModel>> result = new ResResult<List<MulCropStatsModel>>();
            //统计代码
            string StrSql = "select  DictText FillYear, ";
            StrSql += "Area.FullName AreaName,AreaCode,Area.ParentCode, Area.Coord  ";//
            StrSql += "HouseName   ";//--农户姓名
            StrSql += ",InterCropName   ";//--套中作物名称
            StrSql += ",SowTime   ";//--播种时间
            StrSql += ",HarvestTime   ";//--收获时间
            StrSql += ",ISNULL(InterArea,0)  InterArea ";//--套种面积（亩）			
            StrSql += ",ISNULL(UseSeed,0)  UseSeed ";//--用种量（公斤 / 亩）	
            StrSql += ",ISNULL(SeedPrice,0)  SeedPrice ";//--种子价格（元 / 公斤）	
            StrSql += ",ISNULL(AddFertili,0)  AddFertili ";//--因套种增加的化肥用量（公斤 / 亩）	
            StrSql += ",ISNULL(FertilizerPrice,0)  FertilizerPrice ";//--化肥价格（元 / 公斤）	
            StrSql += ",ISNULL(FullGrowEmploy,0)  FullGrowEmploy ";//--全生育期用工量（个 / 亩）	
            StrSql += ",ISNULL(LocalWages,0)  LocalWages ";//--当地工价（元 / 个）	
            StrSql += ",ISNULL(HarvestYield,0)  HarvestYield ";//--收获产量（公斤 / 亩）	
            StrSql += ",ISNULL(SalePrice,0)  SalePrice ";//--销售单价（元 / 公斤）	
            StrSql += "from[dbo].[T_Report] Report inner join T_MulCropIncome MulCropIncome on Report.Id = MulCropIncome.ReportId  ";
            StrSql += "left join T_Area Area on Area.Code = Report.AreaCode left join T_Dict Dict on Report.FillInYear = Dict.DictValue and Dict.GroupCode = 'ReportYear'  ";
            StrSql += "where  Report.FillInYear =@FillInYear  and  Report.Status=@ReportStatus  ";
            if (model.AreaCode != "530000000000")
            {
                StrSql += " and Area.ParentCode =  @AreaCode ";
            }
            StrSql += " order by AreaCode";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var paramlist = new DynamicParameters();
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    paramlist.Add("ReportStatus",model.Status);
                    List<MulCropStatsModel> list = new List<MulCropStatsModel>();
                    List<MulCropStatsModel> listResult = new List<MulCropStatsModel>();
                    List<MulCropStatsModel> list2 = new List<MulCropStatsModel>();
                    listResult = dbCon.Query<MulCropStatsModel>(StrSql, paramlist).ToList();
                    if (model.AreaCode == DefaultKey.ProvinceCode)
                    {
                        AreaServices areaServices = new AreaServices();
                        List<AreaListModel> areaList = new List<AreaListModel>();
                        areaList = areaServices.GetAreaCodeList(model.AreaCode);
                        foreach (var item in areaList)
                        {
                            list = listResult.Where(s => s.ParentCode == item.AreaCode).ToList();
                            int count = list.Count;
                            MulCropStatsModel m = new MulCropStatsModel()
                            {
                                InterArea = 0,
                                AddFertili = 0,
                                FertilizerPerMu = 0,
                                FertilizerPrice = 0,
                                FullGrowEmploy = 0,
                                HarvestYield = 0,
                                LocalWages = 0,
                                SalePrice = 0,
                                SeedPrice = 0,
                                UseSeed = 0,AveIncome=0
                            };
                            m.Coord = item.Coord; m.AreaName = item.AreaName;
                            m.AreaCode = item.AreaCode;
                            if (count>0)
                            {
                                foreach (var item1 in list)
                                {
                                    m.InterArea += item1.InterArea/count;
                                    m.AddFertili += item1.AddFertili / count;
                                    m.FertilizerPerMu += item1.FertilizerPerMu / count;
                                    m.FertilizerPrice += item1.FertilizerPrice / count;
                                    m.FullGrowEmploy += item1.FullGrowEmploy / count;
                                    m.HarvestYield += item1.HarvestYield / count;
                                    m.LocalWages += item1.LocalWages / count;
                                    m.SalePrice += item1.SalePrice / count;
                                    m.SeedPrice += item1.SeedPrice / count;
                                    m.UseSeed += item1.UseSeed / count;
                                }
                            }
                            list2.Add(m);
                        }
                       
                    }
                    else
                    {
                        list2 = listResult;
                    }
                    if (list2 .Count>0)
                    {
                        foreach (var MulCrop in list2)
                        {
                            MulCrop.AveIncome = decimal.Round(Decimal.Parse(((MulCrop.HarvestYield * MulCrop.SalePrice) - (MulCrop.UseSeed * MulCrop.SeedPrice + MulCrop.AddFertili * MulCrop.FertilizerPrice + MulCrop.FullGrowEmploy * MulCrop.LocalWages)).ToString()), 1); 
                            if (MulCrop.InterArea > 0)
                            {
                                MulCrop.TZZC = 0;
                                string s = ((MulCrop.UseSeed * MulCrop.SeedPrice + MulCrop.AddFertili * MulCrop.FertilizerPrice + MulCrop.FullGrowEmploy * MulCrop.LocalWages) / MulCrop.InterArea).ToString();
                                if (!string.IsNullOrEmpty(s))
                                {
                                    MulCrop.TZZC = decimal.Round(Decimal.Parse(s), 1);
                                }
                                MulCrop.TZSR = decimal.Round(Decimal.Parse((MulCrop.SalePrice * MulCrop.HarvestYield / MulCrop.InterArea).ToString()), 1);
                            }

                            
                        }
                        result.model = list2;
                        result.status = DefaultKey.SuccessResult;
                        result.message = "获取套种作物收益情况统计数据成功！";
                    }
                    else
                    {
                        result.status = DefaultKey.InfoResult;
                        result.message = "获取套种作物收益情况统计数据为空！";
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取套种作物收益情况统计数据失败", ex);
            }
            return result;
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(MulNowModel model)
        {
            ReportServices reportServices = new ReportServices();
            model.FillInYear = dict.GetKey(model.FillInYear);
            string ReportId = reportServices.GetReportId(model.ReportCode, model.AreaCode, model.FillInYear);
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\Excel.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulCropIncome") as HSSFSheet;//读取模板所在sheet位置
            List<MulCropIncomeEntity> pagelist = GetList(ReportId).model;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(1).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(1).CellStyle = style;//对第二行第一单元格设置原来样式

            for (var i = 0; i < pagelist.Count; i++)
            {
                HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 3);
                var Total = (pagelist[i].HarvestYield * pagelist[i].SalePrice) - ((pagelist[i].UseSeed * pagelist[i].SeedPrice) + (pagelist[i].AddFertili * pagelist[i].FertilizerPrice) + (pagelist[i].FullGrowEmploy * pagelist[i].LocalWages));
                datarow.CreateCell(0).SetCellValue(pagelist[i].HouseName == null ? "" : pagelist[i].HouseName.ToString());
                datarow.CreateCell(1).SetCellValue(pagelist[i].InterCropName == null ? "" : dict.GetPZName(pagelist[i].InterCropName.ToString(), "InterCrop"));
                datarow.CreateCell(2).SetCellValue(pagelist[i].SowTime == null ? "" : pagelist[i].SowTime.ToString());
                datarow.CreateCell(3).SetCellValue(pagelist[i].HarvestTime == null ? "" : pagelist[i].HarvestTime.ToString());
                datarow.CreateCell(4).SetCellValue(pagelist[i].InterArea == null ? "" : Common.GetOne(pagelist[i].InterArea));
                datarow.CreateCell(5).SetCellValue(pagelist[i].UseSeed == null ? "" : Common.GetOne(pagelist[i].UseSeed));
                datarow.CreateCell(6).SetCellValue(pagelist[i].SeedPrice == null ? "" : Common.GetOne(pagelist[i].SeedPrice));
                datarow.CreateCell(7).SetCellValue(pagelist[i].AddFertili == null ? "" : Common.GetOne(pagelist[i].AddFertili));
                datarow.CreateCell(8).SetCellValue(pagelist[i].FertilizerPrice == null ? "" : Common.GetOne(pagelist[i].FertilizerPrice));
                datarow.CreateCell(9).SetCellValue(pagelist[i].FullGrowEmploy == null ? "" : Common.GetOne(pagelist[i].FullGrowEmploy));
                datarow.CreateCell(10).SetCellValue(pagelist[i].LocalWages == null ? "" : Common.GetOne(pagelist[i].LocalWages));
                datarow.CreateCell(11).SetCellValue(pagelist[i].HarvestYield == null ? "" : Common.GetOne(pagelist[i].HarvestYield));
                datarow.CreateCell(12).SetCellValue(pagelist[i].SalePrice == null ? "" : Common.GetOne(pagelist[i].SalePrice));
                datarow.CreateCell(13).SetCellValue(Common.GetOne(Total));
                //for (var j = 0; j <= 28; j++)
                //{
                //    datarow.GetCell(j).CellStyle = style;
                //}
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 导出统计数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream MethodStats(List<MulCropStatsModel> list)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\ExcelStats.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulCropIncome") as HSSFSheet;//读取模板所在sheet位置
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            //row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式
            var styleAreaOne = Common.GetOneAreaCellStyle(templateBook, style);
            if (list.Count > 0)
            {
                AreaServices area = new AreaServices();
                var areaList = area.GetPrefectureList();
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 3);
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());
                    datarow.CreateCell(1).SetCellValue(list[i].HouseName == null ? "" : list[i].HouseName.ToString());
                    datarow.CreateCell(2).SetCellValue(list[i].InterCropName == null ? "" : list[i].InterCropName.ToString());
                    datarow.CreateCell(3).SetCellValue(list[i].SowTime == null ? "" : list[i].SowTime.ToString());
                    datarow.CreateCell(4).SetCellValue(list[i].HarvestTime == null ? "" : list[i].HarvestTime.ToString());
                    datarow.CreateCell(5).SetCellValue(list[i].InterArea == null ? 0.0 : Convert.ToDouble(list[i].InterArea));
                    datarow.CreateCell(6).SetCellValue(list[i].UseSeed == null ? 0.0 : Convert.ToDouble(list[i].UseSeed));
                    datarow.CreateCell(7).SetCellValue(list[i].SeedPrice == null ? 0.0 : Convert.ToDouble(list[i].SeedPrice));
                    datarow.CreateCell(8).SetCellValue(list[i].AddFertili == null ? 0.0 : Convert.ToDouble(list[i].AddFertili));
                    datarow.CreateCell(9).SetCellValue(list[i].FertilizerPrice == null ? 0.0 : Convert.ToDouble(list[i].FertilizerPrice));
                    //datarow.CreateCell(10).SetCellValue(list[i].FertilizerPerMu == null ? 0.0 : list[i].FertilizerPerMu.ToString());
                    datarow.CreateCell(10).SetCellValue(list[i].FullGrowEmploy == null ? 0.0 : Convert.ToDouble(list[i].FullGrowEmploy));
                    datarow.CreateCell(11).SetCellValue(list[i].LocalWages == null ? 0.0 : Convert.ToDouble(list[i].LocalWages));
                    datarow.CreateCell(12).SetCellValue(list[i].HarvestYield == null ? 0.0 : Convert.ToDouble(list[i].HarvestYield));
                    datarow.CreateCell(13).SetCellValue(list[i].SalePrice == null ? 0.0 : Convert.ToDouble(list[i].SalePrice));
                    datarow.CreateCell(14).SetCellValue(list[i].AveIncome == null ? 0.0 : Convert.ToDouble(list[i].AveIncome));
                    for (var j = 0; j < 15; j++)
                    {
                        datarow.GetCell(j).CellStyle = styleAreaOne;
                    }
                }
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
    }
}